package com.system.Daos;
import com.system.Models.Student;
import com.system.Models.Studentscore;

import javax.swing.table.DefaultTableModel;
import java.sql.*;

public class StudentDao {
    Student student;

    public StudentDao() {
    }

    ;

    public StudentDao(Student student) {

        this.student = student;

    }

    private DefaultTableModel getTableModel(ResultSet resultSet) throws SQLException {

        // 获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();

        // 获取列数
        int columnCount = metaData.getColumnCount();

        // 创建表格模型
        DefaultTableModel model = new DefaultTableModel(){
            @Override
            public boolean isCellEditable(int row, int column) {
                return false; // 禁止所有单元格的编辑
            }
        };

        // 添加列名
        for (int column = 1; column <= columnCount; column++) {
            model.addColumn(metaData.getColumnName(column));
        }

        // 添加行数据
        while (resultSet.next()) {
            Object[] row = new Object[columnCount];
            for (int column = 1; column <= columnCount; column++) {
                row[column - 1] = resultSet.getObject(column);
            }
            model.addRow(row);
        }

        return model;

    }

    public DefaultTableModel getAllTableModel() throws SQLException {

        Connection con = new MySQLExample().getCon();

        String sql = "SELECT * FROM studentinfo"; // 查询语句

        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        DefaultTableModel model = this.getTableModel(resultSet);

        con.close();
        statement.close();
        resultSet.close();

        return model;

    }

    public boolean addStudent() throws SQLException {
        boolean flag = false;
        String id = this.student.getId();
        String name = this.student.getName();
        String sex = this.student.getSex();

        Connection con = new MySQLExample().getCon();

        String sql = "INSERT INTO `studentinfo` (`id`,`name`, `sex`) VALUES ('" + id + "', '" + name + "','" + sex + "');";

        // 创建Statement对象
        Statement stmt = con.createStatement();

        int rowsAffected = stmt.executeUpdate(sql);


        if (rowsAffected == 1 && new StudentScoreDao(new Studentscore(id,-1.0,-1.0)).addStudentscore()) {
            flag = true;
        }

        // 关闭连接、语句和结果集
        stmt.close();
        con.close();

        return flag;
    }

    public boolean deleteStudent() throws SQLException {
        boolean flag = false;
        String id = this.student.getId();

        Connection con = new MySQLExample().getCon();


        String sql = "DELETE FROM studentinfo WHERE `studentinfo`.`id` = '" + id + "'";

        // 创建Statement对象
        Statement stmt = con.createStatement();

        int rowsAffected = stmt.executeUpdate(sql);

        if (rowsAffected == 1) {
            flag = true;
        }

        // 关闭连接、语句和结果集
        stmt.close();
        con.close();

        return flag;
    }

    public boolean alterStudent() throws SQLException {

        boolean f = false;
        // 构造更新语句

        Connection conn = new MySQLExample().getCon();

        String sql = "UPDATE studentinfo SET name = ?, sex = ? WHERE id = ?";

        // 创建 PreparedStatement 对象，并设置参数
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, this.student.getName()); // 设置新的姓名
        statement.setString(2, this.student.getSex()); // 设置新的性别
        statement.setString(3, this.student.getId()); // 设置要更新的学生的ID

        // 执行更新操作
        int rowsAffected = statement.executeUpdate();

        if (rowsAffected == 1) {
            f = true;
        }

        // 关闭连接
        statement.close();
        conn.close();

        return f;

    }

    public DefaultTableModel searchStudent() throws SQLException {
        Connection con = new MySQLExample().getCon();

        String sql = "SELECT * FROM studentinfo WHERE name LIKE '%"+student.getName()+"%' AND id LIKE '%"+student.getId()+"%' AND sex LIKE '%"+student.getSex()+"%'";

        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        DefaultTableModel model = this.getTableModel(resultSet);

        con.close();
        statement.close();
        resultSet.close();

        return model;
    }

}
